package bd;

import java.util.ArrayList;
import classes.LogErro;

public class GenerateSql
{
    /*atributes*/
    public enum CommandType { CMD_INSERT, CMD_UPDATE, CMD_DELETE };

    private Database fDatabase;
    private String fTable;
    private CommandType fCommandType;
    private ArrayList<String> fFieldName;
    private ArrayList<String> fFieldValue;
    private ArrayList<String> fConditionalWhere;
    private String sMensagemErro;
    private String fSql;

    /*constructor*/
    public GenerateSql(Database bd)
    {
        fDatabase = bd;
        fFieldName = new ArrayList<String>();
        fFieldValue = new ArrayList<String>();
        fConditionalWhere = new ArrayList<String>();
    }

    /*properties*/
    public void setTable(String value)
    {
        fTable = value; 
    }


    public String getTable() 
    { 
        return fTable; 
    }
    
    
    public String getMsgErro()
    {
        return sMensagemErro;
    }


    public void setConditionalWhere(String value)
    {
        fConditionalWhere.add(value); 
    }

    
    public String  getSql() 
    { 
        return fSql; 
    }
    
    
    public void    setSql(String value) 
    { 
        fSql = value; 
    }

    
    public CommandType  getCommandType() 
    { 
        return fCommandType; 
    }
    
    
    public void setCommandType(CommandType value) 
    { 
        fCommandType = value; 
    }

    
    /*methods*/
    public Boolean execute(Boolean autoCommit)
    {
        if(fCommandType == CommandType.CMD_INSERT)
                fSql = getSqlInsert();
        else if( fCommandType == CommandType.CMD_DELETE)
                fSql = getSqlDelete();
        else if( fCommandType == CommandType.CMD_UPDATE)
                fSql = getSqlUpDate();
        

        if( fSql == null)
            return true;
        
        fDatabase.executeCommand(fSql, autoCommit);

        if (fDatabase.Error)
        {
            //LogErro.writeError(fDatabase.ErrorMessage);
            //LogErro.writeErrorSql(fSql);
            sMensagemErro = fDatabase.ErrorMessage;
            return false;
        }
        return true;
    }


    public String getSqlDelete()
    {
        String sWhere;

        /* get where*/
        if (fConditionalWhere.size() != 0)
        {
            sWhere = " WHERE " + fConditionalWhere.get(0);
            for (int i = 1; i < fConditionalWhere.size(); i++)
                sWhere += " AND " + fConditionalWhere.get(i);
        }
        else sWhere = " ";

        return "DELETE FROM " + fTable + sWhere;
    }


    public String getSqlInsert()
    {
        String sSQLFields="";
        String sSQLValues="";
        int cont=0;
        
        try
        {            
            for (int i = 0; i < fFieldName.size(); i++)
            {
                if (!fFieldValue.get(i).equals(""))
                {
                    if (i==0)
                    {
                        sSQLFields = fFieldName.get(0);
                        sSQLValues = "'" + fFieldValue.get(0) + "'";
                    }
                    else
                    {
                            sSQLFields += ", " + fFieldName.get(i);
                            sSQLValues += ", '" + fFieldValue.get(i) + "'";                    
                    }
                    cont++;
                }
            }

            if( cont == 0)
                return null;            
        }
        catch(Exception e)
        {
            sSQLFields = "";
            sSQLValues = "";
            //LogErro.writeError("------------------------------------------------------------------------------------");
            //LogErro.writeError("Exceção da peste: "+e.toString());
            //LogErro.writeError("------------------------------------------------------------------------------------");
        }
        return "INSERT INTO " + fTable + " (" + sSQLFields + ") VALUES (" + sSQLValues + ")";
    }


    public String getSqlUpDate()
    {
        String sSQL;
        String sWhere;

        int cont = 0;
        /* get names and values*/
        sSQL = "";
        for (int i = 0; i < fFieldName.size(); i++)
        {
            if (!fFieldValue.get(i).equals(""))
            {
                if (sSQL.equals(""))
                    sSQL = fFieldName.get(i) + "= '" + fFieldValue.get(i) + "'";
                else
                    sSQL = sSQL + ", " + fFieldName.get(i) + "= '" + fFieldValue.get(i) + "'";
                cont++;
            }
        }

        if( cont == 0)
            return null;
        
        /* get where*/
        if (fConditionalWhere.size() != 0)
        {
            sWhere = " WHERE " + fConditionalWhere.get(0);
            for (int i = 1; i < fConditionalWhere.size(); i++)
                sWhere += " AND "+fConditionalWhere.get(i);
        }
        else
            sWhere = "";

        return "UPDATE " + fTable + " SET " + sSQL + sWhere;
    }


    public void prepare()
    {
        //fTable = "";
        fFieldName.clear();
        fFieldValue.clear();
        fConditionalWhere.clear();
        fSql = "";
    }


    public String removeFormat(String Text)
    {
        String sNotFormat = "";
        char sItem;
        for (int i = 1; i < Text.length(); i++)
        {
            sItem = Text.charAt(i);

            if ((sItem >= '0' && sItem <= '9') || (sItem >= 'A' && sItem <= 'Z') || (sItem >= 'a' && sItem <= 'z'))
                sNotFormat = sNotFormat + sItem;
        }
        return sNotFormat;
    }


    public void setConnection(Database ADb)
    {
        fDatabase = ADb;
    }


    public void setFieldsName(ArrayList<String> AValue)
    {
        fFieldName = AValue;
    }


    public void setFieldsValue(ArrayList<String> AValue)
    {
        fFieldValue = AValue;
    }


    public void setValue(String AValue, String AFieldName, String AFormat, char ASeparatorDecimal, char ASeparatorMilhar)
    {
        try
        {
            if ((AValue == null) && (AValue.equals("")))
                return;
            fFieldName.add(AFieldName);
            fFieldValue.add(AValue);
        }
        catch(Exception e)
        {
            return;
        }
        //fFieldsValue.Add(FormataValue(AValue, AFormat, ASeparatorDecimal, ASeparatorMilhar));
    }


    public void setValue(String AValue, String AFieldName, String AFormat)
    {
        try
        {
            if ((AValue == null) && (AValue.equals("")))
                return;
            fFieldName.add(AFieldName);
            fFieldValue.add(AValue);
        }
        catch(Exception e)
        {
            return;
        }
        //fFieldsValue.Add(FormataValue(AValue, AFormat, ASeparatorDecimal, ASeparatorMilhar));
    }


    public void setValue(double AValue, String AFieldName, String AFormat)
    {
        fFieldName.add(AFieldName);
        try
        {
            fFieldValue.add(String.valueOf(AValue).replace(',', '.'));
        }
        catch(Exception e)
        {
            fFieldValue.add("0.00");
        }
        //fFieldsValue.Add(FormataValue(AValue, AFormat, ASeparatorDecimal, ASeparatorMilhar));
    }


    public void setValue(double AValue, String AFieldName)
    {
        fFieldName.add(AFieldName);
        try
        {
            String temp = String.valueOf(AValue).replace(',', '.');
            fFieldValue.add(temp);
        }
        catch(Exception e)
        {
            fFieldValue.add("0.00");
        }
        //fFieldsValue.Add(FormataValue(AValue, AFormat, ASeparatorDecimal, ASeparatorMilhar));
    }


    public void setValue(Boolean AValue, String AFieldName)
    {
        try
        {
            if ((AValue == null) || (AValue.equals("")))
                return;
            fFieldName.add(AFieldName);
            fFieldValue.add(AValue.toString());
        }
        catch(Exception e)
        {
            return;
        }
    }
    
    
    public void setValue(String AValue, String AFieldName)
    {
        try
        {
            if ((AValue == null) || (AValue.equals("")))
                return;
            fFieldName.add(AFieldName);
            fFieldValue.add(AValue);
        }
        catch(Exception e)
        {
            return;
        }
    }
}

